package com.wutong.teacher_server.controller;

import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.print.attribute.standard.NumberUp;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/inputOut")
public class inputOutController {


    //07版excel超大数据数据导出
    @GetMapping("/SXSSFWorkbook")
    public void SXSSFWorkbookTest() throws Exception {

        long begin = System.currentTimeMillis();
        //新建工作簿 表示SXSSFWorkbook只会保留100条数据在内存中，避免内存溢出
        Workbook hssfWorkbook = new SXSSFWorkbook(1000);
        //新建工作表
        Sheet sheet = hssfWorkbook.createSheet("wutong");
        for (int i = 0;i< 100000;i++){
            Row row1 = sheet.createRow(i);
            for(int j = 0;j < 10;j++){
                Cell cell1 = row1.createCell(j);
                cell1.setCellValue(j);
            }
        }
        FileOutputStream fileOutputStream = new FileOutputStream("D:/IdeaProjects/excel/"+"梧桐d.xslx");
        hssfWorkbook.write(fileOutputStream);
        fileOutputStream.flush();
        fileOutputStream.close();
        //清除临时文件
        ((SXSSFWorkbook)hssfWorkbook).dispose();
        long end = System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000);

    }
    //03版excel最大限制65536行导出
    @GetMapping("/HSSFWorkbook")
    public void HSSFWorkbookTest() throws Exception {
        long begin = System.currentTimeMillis();
        //新建工作簿
        Workbook hssfWorkbook = new HSSFWorkbook();
        //新建工作表
        Sheet sheet = hssfWorkbook.createSheet("wutong");
        for (int i = 0;i< 65536;i++){
            Row row1 = sheet.createRow(i);
            for(int j = 0;j < 10;j++){
                Cell cell1 = row1.createCell(j);
                cell1.setCellValue(j);
            }
        }
        FileOutputStream fileOutputStream = new FileOutputStream("D:/IdeaProjects/excel/"+"梧桐.xsl");
        hssfWorkbook.write(fileOutputStream);
        fileOutputStream.flush();
        fileOutputStream.close();
        long end = System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000);
    }
    //07版excel超大数据数据导入
    @GetMapping("/SXSSFWorkbookRead")
    public void SXSSFWorkbookRead() throws Exception {

        FileInputStream fis = new FileInputStream("D:/IdeaProjects/excel/" + "新建 XLSX 工作表.xlsx");
        //新建工作簿
        Workbook wb = WorkbookFactory.create(fis);
        //获取工作表
        Sheet sheetAt = wb.getSheetAt(0);
        //获取标题
        Row row = sheetAt.getRow(0);
        //列的数量
        int physicalNumberOfCells = row.getPhysicalNumberOfCells();
        for (int cellNum = 0;cellNum < physicalNumberOfCells;cellNum++){
            Cell cell = row.getCell(cellNum);
            if (cell != null) {
                String cellValue = cell.getStringCellValue();
                System.out.print(cellValue + '|');
            }
        }
        //获取内容
        int physicalNumberOfRows = sheetAt.getPhysicalNumberOfRows();
        for (int rowNum = 1;rowNum < physicalNumberOfCells;rowNum ++ ){
            Row rowData = sheetAt.getRow(rowNum);
            if (rowData != null) {
                int numberOfCells = rowData.getPhysicalNumberOfCells();
                for (int cellNum = 0;cellNum < numberOfCells;cellNum++){
                    Cell cell = rowData.getCell(cellNum);
                    if (cell != null) {
                        int cellType = cell.getCellType();
                        String cellValue = "";
                        switch (cellType){
                            case HSSFCell.CELL_TYPE_STRING://字符串
                                String stringCellValue = cell.getStringCellValue();
                                cellValue = stringCellValue;
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC://数字（普通数字、日期）
                                if(HSSFDateUtil.isCellDateFormatted(cell)){//日期
                                    Date dateCellValue = cell.getDateCellValue();
                                    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
                                    String format = dateFormat.format(dateCellValue);
                                    cellValue = format;
                                }else {
                                    double numericCellValue = cell.getNumericCellValue();
                                    cellValue = String.valueOf(numericCellValue);
                                }
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN://布尔值
                                boolean booleanCellValue = cell.getBooleanCellValue();
                                cellValue = String.valueOf(booleanCellValue);
                                break;
                            case HSSFCell.CELL_TYPE_BLANK://null
                                break;
                        }
                        System.out.println(cellValue);
                    }

                }


            }
        }

        fis.close();
    }
    //03版excel数据数据导入
    @GetMapping("/HssfWorkBootRead")
    public void HssfWorkBootRead()throws Exception{
        FileInputStream fis = new FileInputStream("D:/IdeaProjects/excel/" + "03导入.xls");

        //新建工作簿
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fis);
        //获取工作表
        HSSFSheet sheetAt = (HSSFSheet)hssfWorkbook.getSheetAt(0);
        //获取标题
        Row row = sheetAt.getRow(0);
        //列的数量
        int physicalNumberOfCells = row.getPhysicalNumberOfCells();
        for (int cellNum = 0;cellNum < physicalNumberOfCells;cellNum++){
            Cell cell = row.getCell(cellNum);
            if (cell != null) {
                String cellValue = cell.getStringCellValue();
                System.out.print(cellValue + '|');
            }
        }
        //获取图片方式一
        /*List<HSSFPictureData> allPictures = hssfWorkbook.getAllPictures();
        List<HSSFShape> children = sheetAt.getDrawingPatriarch().getChildren();
        int i = 0;
        for (HSSFShape shape : children) {
            HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();

            if (shape instanceof HSSFPicture) {
                HSSFPicture pic = (HSSFPicture) shape;
                int row1 = anchor.getRow1();
                System.out.println(i + "--->" + anchor.getRow1() + ":"
                        + anchor.getCol1());
                int pictureIndex = pic.getPictureIndex()-1;
                HSSFPictureData picData = allPictures.get(pictureIndex);
                PictureData picc = (PictureData)picData;
                //数据
                byte[] data = picc.getData();
                //后缀
                String ext = picc.suggestFileExtension();

                System.out.println(i + "--->" + pictureIndex);
                //savePic(row1, picData);
            }
            i++;
        }*/
        //获取图片方式二
        Map<String, PictureData> pictures1 = getPictures1(sheetAt);


        //获取内容
        int physicalNumberOfRows = sheetAt.getPhysicalNumberOfRows();
        for (int rowNum = 1;rowNum < physicalNumberOfCells;rowNum ++ ){
            Row rowData = sheetAt.getRow(rowNum);
            if (rowData != null) {
                int numberOfCells = rowData.getPhysicalNumberOfCells();
                for (int cellNum = 0;cellNum < numberOfCells;cellNum++){
                    Cell cell = rowData.getCell(cellNum);
                    if (cell != null) {
                        int cellType = cell.getCellType();
                        String cellValue = "";
                        switch (cellType){
                            case HSSFCell.CELL_TYPE_STRING://字符串
                                String stringCellValue = cell.getStringCellValue();
                                cellValue = stringCellValue;
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC://数字（普通数字、日期）
                                if(HSSFDateUtil.isCellDateFormatted(cell)){//日期
                                    Date dateCellValue = cell.getDateCellValue();
                                    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
                                    String format = dateFormat.format(dateCellValue);
                                    cellValue = format;
                                }else {
                                    double numericCellValue = cell.getNumericCellValue();
                                    cellValue = String.valueOf(numericCellValue);
                                }
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN://布尔值
                                boolean booleanCellValue = cell.getBooleanCellValue();
                                cellValue = String.valueOf(booleanCellValue);
                                break;
                            case HSSFCell.CELL_TYPE_BLANK://null
                                break;
                        }
                        System.out.println(cellValue);
                    }

                }


            }
        }

        fis.close();

    }
    /**
     * 获取图片和位置 (xls)
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, PictureData> getPictures1 (HSSFSheet sheet) throws IOException {
        Map<String, PictureData> map = new HashMap<String, PictureData>();
        List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
        for (HSSFShape shape : list) {
            if (shape instanceof HSSFPicture) {
                HSSFPicture picture = (HSSFPicture) shape;
                HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
                PictureData pdata = picture.getPictureData();
                String key = cAnchor.getRow1() + "-" + cAnchor.getCol1(); // 行号-列号
                map.put(key, pdata);
            }
        }
        return map;
    }

    /**
     * 获取图片和位置 (xlsx)
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, PictureData> getPictures2 (XSSFSheet sheet) throws IOException {
        Map<String, PictureData> map = new HashMap<String, PictureData>();
        List<POIXMLDocumentPart> list = sheet.getRelations();
        for (POIXMLDocumentPart part : list) {
            if (part instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) part;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {
                    XSSFPicture picture = (XSSFPicture) shape;
                    XSSFClientAnchor anchor = picture.getPreferredSize();
                    CTMarker marker = anchor.getFrom();
                    String key = marker.getRow() + "-" + marker.getCol();
                    map.put(key, picture.getPictureData());
                }
            }
        }
        return map;
    }


}
